﻿using System;
using System.Globalization;
using Gumini.JulaDB.Provider;

namespace Gumini.JulaDB.Test.Common
{
    public class CustomerOrderTestDataCreator
    {
        private readonly JulaDbConnection _connection;

        public int AddCustomer(string name)
        {
            _connection.ExecuteValidNonQuery(String.Format("insert into Customer (CustomerId, Name) values ({0}, '{1}')", TotalPersonCount, name));
            return TotalPersonCount++;
        }

        public int AddProduct(string name, decimal price)
        {
            _connection.ExecuteValidNonQuery(String.Format("insert into Product (ProductId, Name, Price) values ({0}, '{1}', {2})", TotalProductCount, name, price.ToString(CultureInfo.InvariantCulture)));
            return TotalProductCount++;
        }

        public int AddOrder(int customerId)
        {
            _connection.ExecuteValidNonQuery(String.Format("insert into Order (OrderId, CustomerId) values ({0}, {1})", TotalOrderCount, customerId));
            return TotalOrderCount++;
        }

        public int AddOrderItem(int orderId, int productId, int quantity)
        {
            _connection.ExecuteValidNonQuery(String.Format("insert into OrderItem (OrderId, ProductId, Quantity) values ({0}, {1}, {2})", orderId, productId, quantity));
            return TotalOrderItemCount++;
        }

        public CustomerOrderTestDataCreator(JulaDbConnection connection, bool createSampleData = true)
        {
            _connection = connection;

            TotalPersonCount = 0;
            TotalProductCount = 0;
            TotalOrderCount = 0;
            TotalOrderItemCount = 0;

            _connection.ExecuteValidNonQuery(
              @"create table Customer
                (
                    CustomerId int,
                    Name varchar(100)
                );
                create table Order
                (
                    OrderId int,
                    CustomerId int
                );
                create table OrderItem
                (
                    OrderId int,
                    ProductId int,
                    Quantity decimal
                );
                create table Product
                (
                    ProductId int,
                    Name varchar(100),
                    Price decimal
                )");

            if (createSampleData)
                CreateSampleData();
        }

        private void CreateSampleData()
        {
            AddCustomer("John");
            AddCustomer("Mary");
            AddCustomer("Jonathan");
            AddCustomer("Adam");
            AddCustomer("Ann");
            AddCustomer("Tom");

            AddProduct("Apple", 12.2M);
            AddProduct("Orange", 18.0M);
            AddProduct("Blackberry", 25.99M);
            AddProduct("Banana", 7.99M);

            {
                int orderId = AddOrder(customerId: 0);
                AddOrderItem(orderId: orderId, productId: 0, quantity: 2);
                AddOrderItem(orderId: orderId, productId: 2, quantity: 1);
            }
            {
                int orderId = AddOrder(customerId: 1);
                AddOrderItem(orderId: orderId, productId: 1, quantity: 1);
            }
            {
                int orderId = AddOrder(customerId: 1);
                AddOrderItem(orderId: orderId, productId: 0, quantity: 2);
                AddOrderItem(orderId: orderId, productId: 1, quantity: 5);
            }
            {
                int orderId = AddOrder(customerId: 5);
                AddOrderItem(orderId: orderId, productId: 3, quantity: 3);
            }
        }

        public int TotalPersonCount { get; private set; }
        public int TotalProductCount { get; private set; }
        public int TotalOrderCount { get; private set; }
        public int TotalOrderItemCount { get; private set; }
    }
}
